﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;

using System.Data.SqlClient;
using System.Data.Common;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Microsoft.Practices.EnterpriseLibrary;

using AMS.LINQ;
using AMS.Business;
using AMS.UI;

namespace AMS.Database
{
    public class DataAccess
    {
        static Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase db;

        #region Search
        //public DataSet FindRequests(Int32 status, Int32 requestor, DateTime beginDate, DateTime endDate)
        //{
        //    try
        //    {


        //        SqlCommand comm = new SqlCommand();
        //        comm.CommandType = CommandType.Text;
        //        comm.CommandText = "Select * from request";



        //        return null;
        //    }
        //    catch (Exception)
        //    {
                
        //        throw;
        //    }
        //}
        #endregion

        #region Lists
        public static List<RequestorClass> GetRequestorList()
        {
            try
            {
                var requestors = from r in Common.Requestor().Requestors
                                 orderby r.Naam ascending
                                 select new { RequestorID = r.KlantID, RequestorName = r.Naam, Details = r.Adres + " - " + r.Adres2 + " - " + r.Postnr + " - " + r.PostnrA + " - " + r.Woonplaats + " - " + r.Land };

                List<RequestorClass> requestorClassList = new List<RequestorClass>();
                foreach (var item in requestors)
                {
                    requestorClassList.Add(new RequestorClass(item.RequestorID, item.RequestorName, item.Details));
                }

                return requestorClassList;
            }
            catch (Exception)
            {
                
                throw;
            }

        }

        public static DataSet GetAnalysisMethodList()
        {
            try
            {
                return ExecuteStoredProcedure("GetAnalysisMethodList");
            }
            catch (Exception)
            {
                
                throw;
            }
        }

        public static List<AnalysisMethodUIClass> GetAvailableAnalysisMethodList()
        {
            try
            {
                var available = from a in Common.AnalyseMethod().AnalysisMethods
                                where a.Active == true
                                orderby a.AnalysisMethodName ascending, a.AnalysisMethodScientificVersion ascending
                                select new { AnalysisMethodID = a.AnalysisMethodID, AnalysisMethodName = a.AnalysisMethodName + " - " + a.AnalysisMethodScientificVersion};

                List<AnalysisMethodUIClass> analysisMethodClass = new List<AnalysisMethodUIClass>();
                foreach (var item in available)
                {
                    analysisMethodClass.Add(new AnalysisMethodUIClass(item.AnalysisMethodID, item.AnalysisMethodName));
                }

                return analysisMethodClass;
            }
            catch (Exception)
            {

                throw;
            }
        }

        public static object GetStatusList()
        {
            try
            {
                var status = from s in Common.Status().Status
                             select s;

                return status;
            }
            catch (Exception)
            {
                
                throw;
            }
        }

        public static object GetRequestorListFull()
        {
            try
            {
                // This returns the complete list of details for all the requestors
                var requestors = from r in Common.Requestor().Requestors
                                 select r;

                return requestors;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public static object GetAnalysisMethodGroupList()
        {
            var AnalysisMethodGroup = from a in Common.AnalyseMethodGroup().AnalysisMethodGroups select a;

            return AnalysisMethodGroup;
        }
        #endregion

        #region Connection
        public static Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase CreateConnection()
        {
            try
            {
                db = (Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase)DatabaseFactory.CreateDatabase("AMS");

                return db;
            }
            catch (Exception)
            {

                throw;
            }
        }

        public static void BeginTransaction(out SqlDatabase db, out SqlConnection conn, out SqlTransaction trans)
        {
            try
            {
                db = DataAccess.CreateConnection();
                conn = (SqlConnection)db.CreateConnection();
                conn.Open();
                trans = (SqlTransaction)conn.BeginTransaction();
            }
            catch (Exception)
            {
                throw;
            }
        }

        public static void CommitTransaction(SqlTransaction trans)
        {
            if (trans != null)
            {
                trans.Commit();
            }
        }

        public static void RollBackTransaction(SqlTransaction trans)
        {
            if (trans != null)
            {
                trans.Rollback();
            }
        }
        #endregion

        #region Privates

        /// <summary>
        /// Executes a stored procedure
        /// </summary>
        /// <param name="storedProcedureName"></param>
        /// <returns></returns>
        private static DataSet ExecuteStoredProcedure(String storedProcedureName)
        {
            try
            {
                CreateConnection();

                SqlCommand comm = new SqlCommand();
                comm.CommandType = CommandType.StoredProcedure;
                comm.CommandText = storedProcedureName;

                DataSet ds = db.ExecuteDataSet(comm);

                return ds;
            }
            catch (Exception)
            {
                
                throw;
            }
        }

        
        #endregion
    }
}
